package com.sxt.test;

import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.junit.Test;

import com.sxt.entity.City;
import com.sxt.entity.User;
import com.sxt.util.HibernateSessionFactory;

public class Demo {
	/**
	 * Criteria初步
	 */
	@Test
	public void test1() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		@SuppressWarnings("unchecked")
		List<User> list = c.list();
		for (User user : list) {
			System.out.println(user.getUname() + "--->" + user.getCity().getName());
		}
		session.close();
	}
	
	/**
	 * Restrictions工具类
	 */
	@Test
	public void test2() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		c.add(Restrictions.or(Restrictions.eq("age", 25), Restrictions.like("uname", "%张%")));
		c.add(Restrictions.eq("city", session.get(City.class, 1)));
		@SuppressWarnings("unchecked")
		List<User> list = c.list();
		for (User user : list) {
			System.out.println(user.getUname() + "--->" + user.getCity().getName());
		}
		session.close();
	}
	
	/**
	 * Example查询，封装批量查询
	 */
	@Test
	public void test3() {
		Session session = HibernateSessionFactory.getSession();
		
		//封装查询条件
		User user = new User();
		user.setAge(25);
		@SuppressWarnings("unused")
		City city = new City();
		//user.setCity(city);
		user.setCity((City) session.get(City.class, 1));
		
		Criteria c = session.createCriteria(User.class);
		c.add(Example.create(user));
		//c.add(Example.create(session.get(City.class, 1)));
		
		@SuppressWarnings("unchecked")
		List<User> list = c.list();
		for (User u : list) {
			System.out.println(u.getUname() + "--->" + u.getCity().getName());
		}
		session.close();
	}
	
	/**
	 * 排序
	 */
	@Test
	public void test4() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		c.addOrder(Order.asc("age"));
		@SuppressWarnings("unchecked")
		List<User> list = c.list();
		for (User user : list) {
			System.out.println(user.getUname() + "--->" + user.getAge());
		}
		session.close();
	}
	
	/**
	 * 分页查询
	 */
	@Test
	public void test5() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		
		System.out.println("分页前：");
		@SuppressWarnings("unchecked")
		Iterator<User> it = c.list().iterator();
		while(it.hasNext()) {
			System.out.println(it.next());
		}
		
		System.out.println("分页后：");
		c.setFirstResult(3);
		c.setMaxResults(2);
		@SuppressWarnings("unchecked")
		List<User> list = c.list();
		for (User user : list) {
			System.out.println(user);
		}
		session.close();
	}
	
	/**
	 * 单行单列聚合查询
	 * 求User表中的平均年龄
	 */
	@Test
	public void test6() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		c.setProjection(Projections.avg("age"));
		Object obj = c.uniqueResult();
		System.out.println(obj);
		session.close();
	}
	
	/**
	 * 多行单列
	 * 求User表中的平均年龄和最大年龄
	 */
	@Test
	public void test7() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		
		//由于多次setProjection会覆盖，所以封装到ProjectionList中
		ProjectionList plist = Projections.projectionList();
		plist.add(Projections.avg("age"));
		plist.add(Projections.max("age"));
		c.setProjection(plist);
		Object[] objs = (Object[]) c.uniqueResult();
		System.out.println("平均年龄和最大年龄分别为："+Arrays.toString(objs));
		
		session.close();
	}
	
	/**
	 * 多行多列查询
	 * 求每个城市的平均年龄和最大年龄
	 */
	@Test
	public void test8() {
		Session session = HibernateSessionFactory.getSession();
		Criteria c = session.createCriteria(User.class);
		ProjectionList plist = Projections.projectionList();
		plist.add(Projections.groupProperty("city"));
		plist.add(Projections.avg("age"));
		plist.add(Projections.max("age"));
		c.setProjection(plist);
		
		@SuppressWarnings("unchecked")
		List<Object[]> list = c.list();
		for (Object[] objs : list) {
			System.out.println(((City)objs[0]).getName()+"的平均年龄是：" + Double.parseDouble(objs[1].toString()) + "岁,最大年龄是："+objs[2]+"岁");
		}
		session.close();
	}
}
